﻿CREATE PROCEDURE [dbo].[spMOMPortSubscriptions] ( @OwnerID int )
as
begin
	set nocount on
	declare @message nvarchar(1000)
	set @message = 'MOMPortSubscriptions' + '  ' + convert( varchar, getdate(), 9 )
	RAISERROR (@message, 0, 1) WITH NOWAIT

	-- Create the Sequences based on the ClubPlans
	insert into tblSequence (
		[Description], SequenceCode, PreferredCarrierCode, OwnerID )
	select T1.[club_desc], T1.[club_code], T3.CarrierCode, @OwnerID
	from openquery( MOMPort, 'select T1.club_desc, T1.club_code, T1.ship_via
		from clubplan T1' ) as T1
		left outer join codeCarrier T3 on T1.ship_via = T3.abbrev

	insert into tblSequenceProduct ( SequenceID, ProductID )
	select T3.SequenceID, T2.ProductID
	from openquery( MOMPort, 'select number, club_code from stock where club_code is not null' ) as T1
		join tblProduct T2 on T1.[number] = T2.ItemCode and T2.OwnerID = @OwnerID
		join tblSequence T3 on T1.club_code = T3.SequenceCode and T3.OwnerID = @OwnerID

	if OBJECT_ID('tempdb..##tmpSequenceStep') is null
	begin
		create table ##tmpSequenceStep ( SequenceStepID int, ClubCode varchar(10), step int )
		Create index IDX_tmpSequenceStep1 on ##tmpSequenceStep ( SequenceStepID, ClubCode )
		Create index IDX_tmpSequenceStep2 on ##tmpSequenceStep ( ClubCode )
	end

	-- Create the SequenceSteps based on the ClubProd records
	declare cClub cursor local for
	select club_code, continuous from openquery( MOMPort, 'select T1.club_code, T1.continuous 
		from clubplan T1' ) as T1
	open cClub
	declare @club_code varchar(10)
	declare @continuous bit
	declare @SequenceStepID int
	fetch next from cClub into @club_code, @continuous
	while @@fetch_status = 0
	begin
		print 'Club Code = ' + @club_code
		if @continuous = 1
		begin
			-- MOM Continuous series simply go into a single Sequence step record.
			insert into tblSequenceStep(
				SequenceID, [Delay], RecurrenceTerm, RecurrenceCount, Shipping )
			select T2.SequenceID, 0, T1.[num_cycle], 0, case use_ship when 1 then shipping else null end
			from openquery( MOMPort, 'select club_code, num_cycle, use_ship, shipping
				from clubplan') as T1
				join tblSequence T2 on T1.club_code = T2.SequenceCode and T2.OwnerID = @OwnerID
			where T1.club_code = @club_code
				
			set @SequenceStepID = SCOPE_IDENTITY()

			insert into ##tmpSequenceStep ( SequenceStepID, ClubCode, Step )
			values ( @SequenceStepID, @club_code, 0 )
		
			insert into tblSequenceStepProduct(
				SequenceStepID, ProductID, UnitPrice, Qty )
			select @SequenceStepID, T2.ProductID, 
				case when T1.useprice = 0 then T1.price else null end,
				T1.quantity
			from openquery( MOMPort, 'select club_code, number, quantity, useprice, price
				from clubprod' ) as T1	
				join tblProduct T2 on T1.[number] = T2.ItemCode and T2.OwnerID = @OwnerID
			where T1.club_code = @club_code	
		end
		else
		begin
			-- Fixed length series need to create n Sequence steps
			-- based on the number of special items that ship at specific
			-- cycles.
			declare @specials table ( special int )
			delete from @specials
			insert into @specials ( special )
			select distinct special -- Get the distinct special values from clubprod
				from openquery( MOMPort, 'select club_code, special from clubprod where special > 0' )
				where club_code = @club_code
			union select num_ship -- Get the total number of steps
				from openquery( MOMPort, 'select club_code, num_ship from clubplan')
				where club_code = @club_code
			--union select special -- Get the lowest special
			--	from openquery( MOMPort, 'select club_code, min(special) as special from clubprod where special > 1 group by club_code' )
			--	where club_code = @club_code
			union select 1

			declare cSpecial cursor local for
			select special, ( select max(special) from @specials where special < T1.special ) as lastSpecial
			from @specials T1
			where special > 0
			order by 1
			
			declare @special int
			declare @lastSpecial int
			set @lastSpecial = 1
			declare @prevSequenceStepID int
			set @prevSequenceStepID = null
			open cSpecial
			fetch next from cSpecial into @special, @lastSpecial
			while @@fetch_status = 0
			begin
				insert into tblSequenceStep(
					SequenceID, [Delay], RecurrenceTerm, RecurrenceCount, PrevSequenceStepID, Shipping )
				select T2.SequenceID, 
					case when @special = 1 then 0 else T1.num_cycle end, 
					T1.[num_cycle], @special - coalesce(@lastSpecial, 0), 
					@prevSequenceStepID,
					case T1.use_ship when 1 then T1.shipping else null end
				from openquery( MOMPort, 'select club_code, num_cycle, use_ship, shipping
					from clubplan') as T1
					join tblSequence T2 on T1.club_code = T2.SequenceCode  and T2.OwnerID = @OwnerID
				where T1.club_code = @club_code

				set @SequenceStepID = SCOPE_IDENTITY()
				set @prevSequenceStepID = @SequenceStepID

				if @lastSpecial is null
					insert into ##tmpSequenceStep ( SequenceStepID, ClubCode, Step )
					values ( @SequenceStepID, @club_code, 0 )
				
				insert into ##tmpSequenceStep ( SequenceStepID, ClubCode, Step )
				values ( @SequenceStepID, @club_code, @special )
			
				insert into tblSequenceStepProduct(
					SequenceStepID, ProductID, UnitPrice, Qty )
				select @SequenceStepID, T2.ProductID, 
					case when T1.useprice = 0 then T1.price else null end,
					T1.quantity
				from openquery( MOMPort, 'select club_code, number, quantity, useprice, price, repeat, special
					from clubprod' ) as T1	
					join tblProduct T2 on T1.[number] = T2.ItemCode and T2.OwnerID = @OwnerID
				where T1.club_code = @club_code	
					and ( T1.repeat = 1 or T1.special = @special )
				
				fetch next from cSpecial into @special, @lastSpecial
			end
			close cSpecial
			deallocate cSpecial
		end

		fetch next from cClub into @club_code, @continuous
	end
	close cClub
	deallocate cClub

	-- Create Sequences from the breakouts
	insert into tblSequence (
		[Description], SequenceCode, PreferredCarrierCode, OwnerID )
	select T1.[desc], T1.prod, T2.CarrierCode, @OwnerID
	from openquery(MOMPort, 'select distinct T1.prod, T2.desc, T2.carrier
		from breakout T1 join stock T2 on T1.prod = T2.number') T1
		left outer join codeCarrier T2 on T1.carrier = T2.abbrev

	insert into tblSequenceStep(
		SequenceID, [Delay], RecurrenceTerm, RecurrenceCount )
	select T2.SequenceID, T1.ship_days, 0, 1
	from openquery(MOMPort, 'select * from breakout T1 ') T1
		join tblSequence T2 on T1.prod = T2.SequenceCode and T2.OwnerID = @OwnerID
	group by T2.SequenceID, T1.ship_days

	insert into tblSequenceStepProduct(
		SequenceStepID, ProductID, UnitPrice, Qty )
	select distinct T4.SequenceStepID, T3.ProductID, 
		case when T1.s_price = 1 then T1.price else null end,
		T1.q
	from openquery(MOMPort, 'select * from breakout T1 ') T1
		join tblSequence T2 on T1.prod = T2.SequenceCode and T2.OwnerID = @OwnerID
		join tblProduct T3 on T1.inv = T3.ItemCode and T3.OwnerID = @OwnerID
		join tblSequenceStep T4 on T2.SequenceID = T4.SequenceID
			and T1.ship_days = T4.delay

	insert into tblSequenceproduct(	SequenceID, ProductID )
	select distinct T2.SequenceID, T3.ProductID
	from openquery(MOMPort, 'select * from breakout T1 ') T1
		join tblSequence T2 on T1.prod = T2.SequenceCode and T2.OwnerID = @OwnerID
		join tblProduct T3 on T1.prod = T3.ItemCode and T3.OwnerID = @OwnerID

	-- At this point we have a valid Sequence state.  Everything from here down is simplifying
	-- the Sequences down to a minimum number of steps.

	-- Remove do-nothing Sequences step product records
	delete from T1 
	from tblSequenceStepProduct T1
		join tblProduct T2 on T1.ProductID = T2.ProductID
	where T2.InventoryItem = 0
		and not exists ( select 1 from tblSequenceProduct where ProductID = T1.ProductID )

	-- Eliminate Sequences that are simply continuations of 
	-- a previous Sequence
	declare @SequenceID2 int
	declare @NewSequenceID2 int
	declare @NewSequenceStepID2 int
	declare @ProductID2 int
	declare @SourceCt int

	SimplifySequencesTop:
	set @SequenceID2 = null
	select top 1 @SequenceID2 = T1.SequenceID, 
		@NewSequenceID2 = T4.SequenceID, 
		@NewSequenceStepID2 = T4.SequenceStepID,
		@ProductID2 = T2.ProductID,
		@SourceCt = ( select count(*) from tblSequenceStepProduct where ProductID = T2.ProductID )
	from tblSequence T1
		join tblSequenceProduct T2 on T1.SequenceID = T2.SequenceID
		-- Look for a Sequence step leading to this Sequence
		join tblSequenceStepProduct T3 on T2.ProductID = T3.ProductID
		join tblSequenceStep T4 on T3.SequenceStepID = T4.SequenceStepID
	where not exists ( -- Make sure there isn't an inventory item
						-- attached to any of the trigger products.
		select 1 from tblSequenceProduct T2
			join tblProduct T3 on T2.ProductID = T3.ProductID
		where SequenceID = T1.SequenceID
			and T3.InventoryItem = 1 )
		and T1.SequenceID <> T4.SequenceID
		and T1.OwnerID = @OwnerID

	if @SequenceID2 is not null
	begin
		if @SourceCt = 1 
		begin
			-- There is only one sequence leading to @NewSequenceID2
			-- so we can move the steps from @SequenceID2 to @NewSequenceID2
			update tblSequenceStep set PrevSequenceStepID = @NewSequenceStepID2
			where SequenceID = @SequenceID2
				and PrevSequenceStepID is null

			update tblSequenceStep set SequenceID = @NewSequenceID2
			where SequenceID = @SequenceID2

			update tblProduct set ActiveItem = 0
			where ProductID = @ProductID2

			delete from tblSequenceProduct
			where SequenceID = @SequenceID2

			delete from tblSequence
			where SequenceID = @SequenceID2

			delete from T1
			from tblSequenceStepProduct T1
			where T1.SequenceStepID = @NewSequenceStepID2
				and T1.ProductID = @ProductID2
		end
		else
		begin
			-- There are multiple sequences leading to @SequenceID2 
			-- so we need to copy the steps from @SequenceID2 to @NewSequenceID2
			declare @copySequenceSteps table ( OldStepID int, NewStepID int )
			delete from @copySequenceSteps
			insert into @copySequenceSteps ( OldStepID )
			select SequenceStepID from tblSequenceStep where SequenceID = @SequenceID2

			declare cCopy cursor local for select OldStepID from @copySequenceSteps order by OldStepID
			declare @oldStepID int
			declare @newStepID int
			open cCopy
			fetch next from cCopy into @oldStepID
			while @@fetch_status = 0
			begin
				insert into tblSequenceStep  ( SequenceID, Delay, RecurrenceTerm, RecurrenceCount, PrevSequenceStepID )
				select @NewSequenceID2, T1.Delay, T1.RecurrenceTerm, T1.RecurrenceCount, 
					case when T2.NewStepID is null then @NewSequenceStepID2 else T2.NewStepID end
				from tblSequenceStep T1
					left outer join @copySequenceSteps T2 on T1.PrevSequenceStepID = T2.OldStepID
				where T1.SequenceStepID = @oldStepID

				set @newStepID = SCOPE_IDENTITY()
				update @copySequenceSteps set NewStepID = @newStepID where OldStepID = @oldStepID

				insert into tblSequenceStepProduct ( SequenceStepID, ProductID, UnitPrice, Qty )
				select @newStepID, ProductID, UnitPrice, Qty
				from tblSequenceStepProduct
				where SequenceStepID = @oldStepID
	
				fetch next from cCopy into @oldStepID
			end
			close cCopy
			deallocate cCopy

			delete from T1
			from tblSequenceStepProduct T1
			where T1.SequenceStepID = @NewSequenceStepID2
				and T1.ProductID = @ProductID2

			delete from T1
			from tblSequenceProduct T1
			where T1.SequenceID = @SequenceID2
				and T1.ProductID = @ProductID2
				and not exists ( select 1 from tblSequenceStepProduct where ProductID = @ProductID2 )
		end

		goto SimplifySequencesTop
	end

		
	-- Remove Sequence steps that aren't linked to any products
	declare @EmptySequenceStepID int
	CleanoutEmptySteps:
	set @EmptySequenceStepID = null
	select top 1 @EmptySequenceStepID = SequenceStepID
	from tblSequenceStep T1
		join tblSequence T2 on T1.SequenceID = T2.SequenceID
	where not exists ( select 1 from tblSequenceStepProduct where SequenceStepID = T1.SequenceStepID )
		and T2.OwnerID = @OwnerID

	if @EmptySequenceStepID is not null
	begin
		update T1 set PrevSequenceStepID = T2.PrevSequenceStepID,
			[Delay] = T1.[delay] + T2.[Delay]
		from tblSequenceStep T1
			join tblSequenceStep T2 on T1.PrevSequenceStepID = T2.SequenceStepID
		where T2.SequenceStepID = @EmptySequenceStepID 

		-- Link the MOM club code to a valid sequence step
		update T1 set SequenceStepID = coalesce( T2.PrevSequenceStepID, T3.SequenceStepID )
		from ##tmpSequenceStep T1
			join tblSequenceStep T2 on T1.SequenceStepID = T2.SequenceStepID
			left outer join ( select max( SequenceStepID ) as SequenceStepID, SequenceID,
								coalesce(PrevSequenceStepID, -1) as PrevSequenceStepID
				from tblSequenceStep
				where SequenceStepID <> @EmptySequenceStepID
				group by SequenceID, coalesce(PrevSequenceStepID, -1) )  as T3 
					on coalesce(T2.PrevSequenceStepID, -1) = T3.PrevSequenceStepID
						and T2.SequenceID = T3.SequenceID
		where T2.SequenceStepID = @EmptySequenceStepID
		
		delete from T1
		from tblSequenceStep T1
		where T1.SequenceStepID = @EmptySequenceStepID 

		if (@@rowcount > 0)
			goto CleanoutEmptySteps
	end


	-- Combine "like" steps together
	declare @tmpCombine table ( SequenceStepID int, chksum int )
	insert into @tmpCombine ( SequenceStepID, chksum )
	select T1.SequenceStepID, T2.chksum
	from tblSequenceStep T1
		join ( select SequenceStepID, checksum_agg(checksum(cast(T1.Qty as varchar) + cast(T1.ProductID as varchar) + cast(coalesce(T1.UnitPrice, 0) as varchar))) as chksum
				from tblSequenceStepProduct T1
				group by SequenceStepID ) as T2 on T1.SequenceStepID = T2.SequenceStepID
		join tblSequence T3 on T1.SequenceID = T3.SequenceID
	where T3.OwnerID = @OwnerID
	declare @SequenceStepID3 int
	goto_loop:
	set @SequenceStepID3 = null
	select top 1 @SequenceStepID3 = T2.SequenceStepID 
	from tblSequenceStep T1
			join tblSequenceStep T2 on T2.PrevSequenceStepID = T1.SequenceStepID
			join @tmpCombine T3 on T1.SequenceStepID = T3.SequenceStepID
			join @tmpCombine T4 on T2.SequenceStepID = T4.SequenceStepID
	where T3.chksum = T4.chksum
		and T1.delay = T2.delay
		and T1.RecurrenceTerm = T2.RecurrenceTerm
	if @SequenceStepID3 is not null 
	begin
		update T2 set PrevSequenceStepID = T1.PrevSequenceStepID
		from tblSequenceStep T1
			join tblSequenceStep T2 on T2.PrevSequenceStepID = T1.SequenceStepID
		where T1.SequenceStepID	= @SequenceStepID3

		update T2 set RecurrenceCount = T1.RecurrenceCount + T2.RecurrenceCount
		from tblSequenceStep T1
			join tblSequenceStep T2 on T1.PrevSequenceStepID = T2.SequenceStepID
		where T1.SequenceStepID	= @SequenceStepID3

		update T1 set SequenceStepID = T2.PrevSequenceStepID
		from ##tmpSequenceStep T1
			join tblSequenceStep T2 on T1.SequenceStepID = T2.SequenceStepID
		where T2.SequenceStepID = @SequenceStepID3
		
		delete from tblSequenceStepProduct where SequenceStepID = @SequenceStepID3
		delete from tblSequenceStep where SequenceStepID = @SequenceStepID3

		goto goto_loop
	end

end